package com.swordget.dao.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.swordget.commons.JdbcUtil;
import com.swordget.dao.TableDefinitionDao;
import com.swordget.entity.ColumnDesign;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * on 2020/4/4 16:25
 *
 * @author David
 */
@Repository
public class TableDefinitionDaoImp implements TableDefinitionDao {

    @Autowired
    private JdbcUtil jdbcUtil;
    
    @Autowired
    private DruidDataSource dataSource;

    /**
     * TableDefinitionDaoImp log
     */
    private final static Logger LOG = LoggerFactory.getLogger(TableDefinitionDaoImp.class);

    /**
     * @param tableName 表名
     * @param tableId 表id
     * @return
     */
    @Override
    public boolean isExists(String tableName, String tableId) {
        String command = String.format("SELECT COUNT(*) FROM TABLEDESIGN WHERE SERIALNO = '%s' AND TABLENAME = '%s'",
                tableId, tableName);
        int count = jdbcUtil.executeCount(command);
        return count > 0;
    }

    /**
     * @param tableId
     * @return
     */
    @Override
    public String isExists(String tableId) {
        String command = String.format("SELECT TABLENAME FROM TABLEDESIGN WHERE SERIALNO = '%s'",
                tableId);
        String tableName = (String) jdbcUtil.findOne(command);
        return tableName;
    }


    /**
     * @param tableName 表名
     * @param tableId 表id
     * @return
     */
    @Override
    public boolean clearData(String tableName, String tableId) {
        //首先清除表定义数据
        String deleteTableDesign = String.format("DELETE FROM TABLEDESIGN WHERE SERIALNO = '%s' AND TABLENAME = '%s'",
                tableId, tableName);
        String deleteColumnDesign = String.format("DELETE FROM TABLEDEFINITION WHERE TABLEID = '%s'",
                tableId);
        //TODO 清除系统表权限等记录
        boolean isClear = jdbcUtil.executeBatch(deleteTableDesign, deleteColumnDesign);
        return isClear;
    }

    /**
     * @param tableId 表标识
     * @param withHidden 是否包含隐藏列
     * @param withBusiness
     * @return
     */
    @Override
    public List<String> getColumns(String tableId, boolean withHidden, boolean withBusiness) {
        String command= String.format("SELECT COLUMNNAME FROM tabledefinition WHERE TABLEID = '%s' ",
                tableId);
        if (!withHidden) {command += "AND ISHIDDEN = 'N' ";}
        if (!withBusiness) {command += "AND ISBUSINESS = 'N' "; }
        command += "ORDER BY TABLEORDER";
        return jdbcUtil.findOneByList(command);
    }

    /**
     * @param tableId 表标识
     * @param withHidden 是否包含隐藏列
     * @param withBusiness
     * @return
     */
    @Override
    public List<String> getTypes(String tableId, boolean withHidden, boolean withBusiness) {
        String command= String.format("SELECT COLUMNTYPE FROM tabledefinition WHERE TABLEID = '%s' ",
                tableId);
        if (!withHidden) {command += "AND ISHIDDEN = 'N' ";}
        if (!withBusiness) {command += "AND ISBUSINESS = 'N' "; }
        command += "ORDER BY TABLEORDER";
        List<String> types = jdbcUtil.findOneByList(command);
        return types;
    }

    @Override
    public List<String> getCommons(String tableId, boolean withHidden, boolean withBusiness) {
        String command= String.format("SELECT COLUMNCOMMENT FROM tabledefinition WHERE TABLEID = '%s' ",
                tableId);
        if (!withHidden) {command += "AND ISHIDDEN = 'N' ";}
        if (!withBusiness) {command += "AND ISBUSINESS = 'N' "; }
        command += "ORDER BY TABLEORDER";
        List<String> commons = jdbcUtil.findOneByList(command);
        return commons;
    }

    /**
     * 获取指定表详细列信息
     * @param tableId 表标识
     * @param withHidden 是否为隐藏列
     * @param withBusiness
     * @return
     */
    @Override
    public List<ColumnDesign> getColumnDesigns(String tableId, boolean withHidden, boolean withBusiness) {
        String command = String.format("SELECT SERIALNO, COLUMNNAME, COLUMNTYPE, COLUMNWIDTH, COLUMNPOINT, " +
                "COLUMNCOMMENT, SEARCH, COMBOURL, TEXTTYPE, NOTNULL, ISHIDDEN, DATEFORMAT, ISBUSINESS, NOTREPEAT FROM " +
                "TABLEDEFINITION  WHERE TABLEID = '%s' ", tableId);
        if (!withHidden) {command += "AND ISHIDDEN = 'N' ";}
        if (!withBusiness) {command += "AND ISBUSINESS = 'N' ";}
        command += "ORDER BY TABLEORDER";

        List<ColumnDesign> designs = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             Statement statement = conn.createStatement();
             ResultSet resultSet = statement.executeQuery(command)) {
            while (resultSet.next()) {
                ColumnDesign  columnDesign= ColumnDesign.builder()
                        .serialNo(resultSet.getString(1))
                        .name(resultSet.getString(2))
                        .type(resultSet.getString(3))
                        .width(resultSet.getInt(4))
                        .point(resultSet.getInt(5))
                        .comment(resultSet.getString(6))
                        .search(resultSet.getString(7))
                        .comboUrl(resultSet.getString(8))
                        .contextType(resultSet.getString(9))
                        .notNull(resultSet.getString(10))
                        .isHidden(resultSet.getString(11))
                        .dateFormat(resultSet.getString(12))
                        .isBusiness(resultSet.getString(13))
                        .notRepeat(resultSet.getString(14))
                        .build();

                designs.add(columnDesign);
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return designs;
    }

    /**
     * @param tableId 表标识
     * @return
     */
    @Override
    public String getCheckFilePath(String tableId) {
        String command = String.format("SELECT CHECKFILE FROM TABLEDESIGN WHERE SERIALNO = '%s'", tableId);
        return (String) jdbcUtil.findOne(command);
    }

    @Override
    public String getTableComment(String tableId) {
        String command = String.format("SELECT TABLENCOM FROM TABLEDESIGN WHERE SERIALNO = '%s'", tableId);
        return (String) jdbcUtil.findOne(command);
    }


}
